package com.midi.wholesale.main;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.midi.wholesale.main.*;
import com.mysql.jdbc.Statement;


public class addElements {
	
private static Statement st = null;
private static Statement stOne = null;
private static Statement stTwo = null;
private static Statement stFour = null;
private static Statement stFive = null;


private static PreparedStatement stSix = null;
private static PreparedStatement stSeven = null;
private static PreparedStatement stEight = null;
private static PreparedStatement stNine = null;
private static PreparedStatement stTen = null;
private static PreparedStatement stEleven = null;
private static PreparedStatement stTwlve = null;
private static PreparedStatement stThirteen = null;
private static PreparedStatement stFourteen = null;
private static PreparedStatement stFifteen = null;
private static PreparedStatement stSixteen = null;
private static PreparedStatement stSeventeen = null;

private static int val;
private static int regionid ;
	
// When initializing the Frame load data to list
		public static Object[] loadDataAddElementsFrame(){
			

			
			try {
			st = (Statement) Main.dbConnection.createStatement();
			stOne = (Statement) Main.dbConnection.createStatement();
			stTwo = (Statement) Main.dbConnection.createStatement();
			
			} catch (SQLException e) {
		System.out.println("connection error in addElement Class");
			e.printStackTrace();
			}
		
// select data from agency, region, shop
		
		ResultSet agencyrs = null;
		ArrayList agencyList = new ArrayList();
		ResultSet regionrs = null;
		ArrayList  regionList = new ArrayList();
		ResultSet shoprs = null;
		ArrayList  shopList = new ArrayList();
		Object [] listObject= new Object[3];
		
		try {
			agencyrs = st.executeQuery("select name from agency");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while(agencyrs.next()){
			//	agencyList=agencyList+agencyrs.next()+",";
				agencyList.add(agencyrs.getString("name"));
				
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			regionrs = stOne.executeQuery("select name from regions");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while(regionrs.next()){
				regionList.add(regionrs.getString("name"));
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		try {
			shoprs = stTwo.executeQuery("select name from shops");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			while(shoprs.next()){
				shopList.add(shoprs.getString("name"));
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		listObject[0]=agencyList;
		listObject[1]=regionList;
		listObject[2]=shopList;
		
		return listObject;
	}
	
//Edit agency list with database data
	public static void editAgency(String agencyName, String actionPerformed){
		
		

//	      stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
//	      stmt.setString(1, name);
//	      stmt.setString(2, email);
//	      stmt.executeUpdate();
			
		
		
		if(actionPerformed=="add"){
			
			 try {
				 
				 stSeven = (PreparedStatement) Main.dbConnection.prepareStatement("INSERT INTO agency (name) values (?)");
				 stSeven.setString(1, agencyName);
				 stSeven.executeUpdate();
				 //val = st.executeUpdate("INSERT INTO agency(name) VALUES ('"+agencyName+"')");
			} catch (SQLException e) {
				System.out.println("error in add agency");
				e.printStackTrace();
			}
			
		}
		
		if(actionPerformed=="remove"){ 
			
			try {
				  String sql = "delete from agency where name = ?";
				  stNine = Main.dbConnection.prepareStatement(sql);
				  stNine.setString(1,agencyName );
				  int del = stNine.executeUpdate();
				
				//val = st.executeUpdate("delete from agency where name='"+agencyName+"'");
			} catch (SQLException e) {
				System.out.println("error in add agency");
				e.printStackTrace();
			}
			
			
		}
	 	
	}
//Edit region list with database data
		public static void editRegion(String regionName, String actionPerformed){
			if(actionPerformed=="add"){
				
				 try {
					 stEight = (PreparedStatement) Main.dbConnection.prepareStatement("INSERT INTO regions (name) values (?)");
					 stEight.setString(1, regionName);
					 stEight.executeUpdate();
					 
					//val = st.executeUpdate("INSERT INTO regions(name) VALUES ('"+regionName+"')");
				} catch (SQLException e) {
					System.out.println("error in add region");
					e.printStackTrace();
				}
				
			}
			
			if(actionPerformed=="remove"){ 
				
				try {
					  String sql = "delete from regions where name = ?";
					  stTen = Main.dbConnection.prepareStatement(sql);
					  stTen.setString(1,regionName );
					  int del = stTen.executeUpdate();	
					
					//val = st.executeUpdate("delete from regions where name='"+regionName+"'");
				} catch (SQLException e) {
					System.out.println("error in remove regions");
					e.printStackTrace();
				}
	
			}

		}
		
//Edit shop list with database data
		public static void editShop(String shopName,String shopRegion, String address, String contDetails){

			ResultSet result = null;
			try {
				stFive = (Statement) Main.dbConnection.createStatement();		
			} catch (SQLException e) {
			System.out.println("connection error in editshopdetails Class");
				e.printStackTrace();
			}

			try {
				result = stFive.executeQuery("select id from regions where name='"+shopRegion+"'");
			} catch (SQLException e) {
				System.out.println("No such region regarding supplied region name");
				e.printStackTrace();
			}
			
			try {
				result.next();
			regionid=result.getInt("id");
			System.out.println("region id is"+regionid);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				
				 stSix = (PreparedStatement) Main.dbConnection.prepareStatement("INSERT INTO shops (region_id,name,address,phone) values (?,?,?,?)");
				 stSix.setLong(1, regionid);
				 stSix.setString(2, shopName);
				 stSix.setString(3, address);
				 stSix.setString(4, contDetails);
				 stSix.executeUpdate();
				
				//System.out.println("values are"+regionid+" "+shopName+" "+address+" "+contDetails);
				//val = stSix.executeUpdate("INSERT INTO shops(region_id,name,address,phone) VALUES ('"+regionid+"','"+shopName+"','"+address+"','"+contDetails+"')");     
			} catch (SQLException e) {
				System.out.println("error in add shops");
				e.printStackTrace();
			}
			
					
		}
		
// Get all the regions from the database
		
	public static ArrayList getAllRegions(){
		try {
			stFour = (Statement) Main.dbConnection.createStatement();
			
			
		} catch (SQLException e) {
		System.out.println("connection error in addElement Class");
			e.printStackTrace();
		}
		
		ResultSet regionAllRs = null;
		ArrayList agencyALL = new ArrayList();
		try {
			regionAllRs = stFour.executeQuery("select name from regions");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			while(regionAllRs.next()){
			//	agencyList=agencyList+agencyrs.next()+",";
				agencyALL.add(regionAllRs.getString("name"));
				
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			System.out.println("Error in region all getting query");
			e.printStackTrace();
		}
		return agencyALL;	
	}
	
// Remove shop from the data base	
	public static void removeShop(String shopName, String regionName){
		
		//System.out.println("Values are here "+shopName+" "+regionName);
		
		try {
			stFive= (Statement) Main.dbConnection.createStatement();
			
			
		} catch (SQLException e) {
		System.out.println("connection error removeshop");
			e.printStackTrace();
		}
		
			
			try {
				  
				String sqlTwo="select id from regions where name= ?";				  
				//  ResultSet newRs=stThirteen.executeQuery("select id from regions where name= ?");
				  stThirteen=Main.dbConnection.prepareStatement(sqlTwo);
				  stThirteen.setString(1, regionName);
				  ResultSet newRs = stThirteen.executeQuery();
				  newRs.next();
				String sql = "DELETE FROM shops WHERE name = ? AND region_id = ?";
				  
				  stEleven = Main.dbConnection.prepareStatement(sql);
				  stEleven.setString(1,shopName );
				  stEleven.setString(2, newRs.getString("id"));
				  int del = stEleven.executeUpdate();	
				
				//val = stFive.executeUpdate("delete from shops where name='"+shopName+"'");
			} catch (SQLException e) {
				System.out.println("error in remove shops");
				e.printStackTrace();
			}		
	}
	
	public static ArrayList getNewList(){
		
		ArrayList newArr= new ArrayList();
		String sqlNew="SELECT s.name as shop_name,r.name as region_name FROM shops s INNER JOIN regions r ON r.id=s.region_id";		
		
		//  ResultSet newRs=stThirteen.executeQuery("select id from regions where name= ?");
		  try {
			stSeventeen=Main.dbConnection.prepareStatement(sqlNew);
			ResultSet rs = stSeventeen.executeQuery();
			
			while(rs.next()){
				newArr.add(rs.getString("shop_name")+"-"+rs.getString("region_name"));
				
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return newArr;
	}
	//get the regions map to shops
	
	public static String getRegionsMapToShop(String shopName){
		//System.out.print(" here name" + shopName+"");
		
		String sqlStatement = "SELECT r.name FROM regions r INNER JOIN shops s ON s.region_id=r.id WHERE s.name = ?";
		String returnRegions = null; 
		try {
			stTwlve = Main.dbConnection.prepareStatement(sqlStatement);
			stTwlve.setString(1,shopName );
			 ResultSet rs = stTwlve.executeQuery();
			 rs.next();
		 returnRegions=rs.getString("name");
		 //System.out.print("################### here" + returnRegions+"##");
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		  return returnRegions;
		
//		ArrayList regionsMapToShop=new ArrayList();
//		
//		
//		try {
//			String sqlStatement = "SELECT r.name FROM regions r INNER JOIN shops s ON s.region_id=r.id WHERE s.name = ?";
//			  stTwlve = Main.dbConnection.prepareStatement(sqlStatement);
//			  stTwlve.setString(1,shopName );
//			  ResultSet rs = stTwlve.executeQuery();
//			 // ResultSet rs=stTwlve.getResultSet();
//			  String returnRegions="";
//			  
//			  while(rs.next()){
//				  
//				  returnRegions=rs.getString("name");
//				  System.out.println("Return region names are"+returnRegions);
//				  regionsMapToShop.add(returnRegions);
//				  
//			  }
//			
//			
//		} catch (SQLException e) {
//			System.out.println("error in region mapping shops");
//			e.printStackTrace();
//		}
//		
//		
//	return regionsMapToShop;
	}
	
	
	public static ArrayList getRegionIdAll (String shopName){
		
			
		ArrayList regionsMapToShop=new ArrayList();
		
		
		try {
			String sqlStatement = "SELECT r.name FROM regions r INNER JOIN shops s ON s.region_id=r.id WHERE s.name = ?";
			  stTwlve = Main.dbConnection.prepareStatement(sqlStatement);
			  stTwlve.setString(1,shopName );
			  ResultSet rs = stTwlve.executeQuery();
			 // ResultSet rs=stTwlve.getResultSet();
			  String returnRegions="";
			  
			  while(rs.next()){
				  
				  returnRegions=rs.getString("name");
				  System.out.println("Return region names are"+returnRegions);
				  regionsMapToShop.add(returnRegions);
				  
			  }
		
	}
		catch (SQLException e) {
			System.out.println("error in region mapping shops");
			e.printStackTrace();
		}
		return regionsMapToShop;
		
		}
	
public static void deleteShopMapToRegion (String regionName){
ResultSet regionRs;
	
// try {
//	PreparedStatement updateStatement = Main.dbConnection.prepareStatement("UPDATE shops SET region_id='0' WHERE='(SELECT id FROM regions WHERE name=?)'");
//	updateStatement.setString(1, regionName);
//	updateStatement.executeUpdate();
//} catch (SQLException e) {
//	// TODO Auto-generated catch block
//	e.printStackTrace();
//}
// 
	 
try {
	
	String sqlNeed="select id from regions where name=?";
	PreparedStatement updateStatement = Main.dbConnection.prepareStatement(sqlNeed);
	updateStatement.setString(1, regionName);
	regionRs=updateStatement.executeQuery();
	regionRs.next();
	int regionId=regionRs.getInt("id");
	System.out.println("**************"+regionId);
	
	String sqlNeedTwo="Update shops set region_id = '0' where region_id = ?";
	PreparedStatement updateStatementNew = Main.dbConnection.prepareStatement(sqlNeedTwo);
	updateStatementNew.setInt(1, regionId);
	updateStatementNew.executeUpdate();
	
	
	
	
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
			

	
}	
public static String getPassword(int id){
	ResultSet passwordResult;
	String PW = null;
	try {
		Statement newST=(Statement) Main.dbConnection.createStatement();
		passwordResult = newST.executeQuery("select value from config where id='"+id+"'");
		passwordResult.next();
        PW=passwordResult.getString("value");
        
		
	} catch (SQLException e) {
		System.out.println("Error in password checker");
		e.printStackTrace();
	}
	return PW;
}

	
}
